import os
import openpyxl
from openpyxl.styles import Font
from django.conf import settings
from django.http import HttpResponse
from io import BytesIO


class ExcelHandler:
    @staticmethod
    def get_model_fields(model_class):
        """获取模型的字段列表（排除自增ID和多对多字段）"""
        fields = []
        for field in model_class._meta.fields:
            if not field.auto_created and not field.many_to_many:
                fields.append({
                    'name': field.name,
                    'verbose_name': field.verbose_name,
                    'field': field
                })
        return fields
    
    @staticmethod
    def create_template(model_class, filename="template.xlsx"):
        """生成导入模板"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = model_class._meta.verbose_name_plural or model_class.__name__
        
        # 获取字段信息
        fields = ExcelHandler.get_model_fields(model_class)
        
        # 写入表头
        for col, field in enumerate(fields, 1):
            cell = ws.cell(row=1, column=col)
            cell.value = field['verbose_name']
            cell.font = Font(bold=True)
        
        # 保存到内存
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        
        # 构建响应
        response = HttpResponse(buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = f'attachment; filename="{filename}"'
        return response
    
    @staticmethod
    def export_data(queryset, model_class, filename="data.xlsx"):
        """导出数据到Excel"""
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = model_class._meta.verbose_name_plural or model_class.__name__
        
        # 获取字段信息
        fields = ExcelHandler.get_model_fields(model_class)
        
        # 写入表头
        for col, field in enumerate(fields, 1):
            cell = ws.cell(row=1, column=col)
            cell.value = field['verbose_name']
            cell.font = Font(bold=True)
        
        # 写入数据
        for row, obj in enumerate(queryset, 2):
            for col, field in enumerate(fields, 1):
                field_name = field['name']
                # 处理外键字段
                if hasattr(getattr(obj, field_name), 'pk'):
                    cell_value = str(getattr(obj, field_name))
                else:
                    cell_value = getattr(obj, field_name)
                ws.cell(row=row, column=col).value = cell_value
        
        # 保存到内存
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)
        
        # 构建响应
        response = HttpResponse(buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = f'attachment; filename="{filename}"'
        return response
    
    @staticmethod
    def import_data(file, model_class, form_class=None):
        """导入Excel数据到数据库"""
        errors = []
        success_count = 0
        
        try:
            wb = openpyxl.load_workbook(file, data_only=True)
            ws = wb.active
            fields = ExcelHandler.get_model_fields(model_class)
            field_names = [f['name'] for f in fields]
            
            # 验证表头
            header_row = [cell.value for cell in ws[1]]
            expected_headers = [f['verbose_name'] for f in fields]
            if header_row != expected_headers:
                errors.append(f"表头不匹配，期望: {expected_headers}，实际: {header_row}")
                return {'errors': errors, 'success_count': success_count}
            
            # 处理数据行
            for row_num, row in enumerate(ws.iter_rows(min_row=2, values_only=True), 2):
                if not any(row):  # 跳过空行
                    continue
                
                data = dict(zip(field_names, row))
                
                # 处理外键字段（这里简化处理，实际可能需要根据显示值查询ID）
                for field in fields:
                    if hasattr(field['field'], 'remote_field') and field['field'].remote_field:
                        fk_model = field['field'].remote_field.model
                        display_value = data[field['name']]
                        if display_value:
                            try:
                                fk_obj = fk_model.objects.get(**{fk_model._meta.get_field('name').name: display_value})
                                data[field['name']] = fk_obj.pk
                            except fk_model.DoesNotExist:
                                errors.append(f"第{row_num}行：{field['verbose_name']}不存在 {display_value}")
                                continue
                
                # 使用表单验证数据
                if form_class:
                    form = form_class(data=data)
                    if form.is_valid():
                        form.save()
                        success_count += 1
                    else:
                        errors.append(f"第{row_num}行：{form.errors}")
                else:
                    model_class.objects.create(**data)
                    success_count += 1
        
        except Exception as e:
            errors.append(f"导入失败：{str(e)}")
        
        return {'errors': errors, 'success_count': success_count}